package com.kcsj.dao;
import com.kcsj.dao.User.CustomerDao;
import com.kcsj.domain.DelayedApplication;
import com.kcsj.domain.Order;
import com.kcsj.domain.PayType;
import com.kcsj.domain.ProductModel;
import com.kcsj.domain.user.Customer;
import com.kcsj.service.PayTypeService;
import com.kcsj.util.Condition;
import com.kcsj.util.JdbcHelper;

import java.sql.*;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

/**
 * @author 傅立贤
 * 25th Nov. 2020
 */

public class OrderDao {
    public static OrderDao orderDao = new OrderDao();

    private OrderDao() {
    }

    public static OrderDao getInstance() {
        return orderDao;
    }
    /**
     * @author 傅立贤
     * @param conn    数据库连接对象
     * @return rowsAffected 影响的行数
     * @throws SQLException
     */
    public Collection<Order> findAll(Connection conn, List<Condition> conditionList) throws SQLException {
        ArrayList<Order> orders = new ArrayList<>();
        //预编译方式进行查询
        StringBuilder sql = new StringBuilder("select * from orderform LEFT JOIN delayedapplication on " +
                "orderform.orderNo = delayedApplication.orderNo");
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql.append(whereClause);
        }
        PreparedStatement preparedStatement = conn.prepareStatement(sql.toString());
        System.out.println(sql.toString());
        ResultSet resultSet = preparedStatement.executeQuery();
        //将查询到的数据存到departments集合中
        while (resultSet.next()){
            String orderNo = resultSet.getString("orderNo");
            String address = resultSet.getString("address");
            String phoneNumber = resultSet.getString("phoneNumber");
            String consigneeName = resultSet.getString("consigneeName");
            Timestamp createDatetime = resultSet.getTimestamp("createDatetime");
            double cost = resultSet.getDouble("cost");
            int payType_id = resultSet.getInt("payType_id");
            PayType payType = PayTypeService.getInstance().find(payType_id);
            String orderStatus = resultSet.getString("orderStatus");
            DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            Collection<ProductModel> productModels
                    = OrderAssProductModelDao.getInstance().find(orderNo, conn);
            int customer_id = resultSet.getInt("customer_id");
            boolean showForCustomer = resultSet.getBoolean("showForCustomer");
            Customer customer = CustomerDao.getInstance().findInfo(customer_id, conn);
            double delayOfMoney  = resultSet.getDouble("delayOfMoney");
            Integer delayDays  = resultSet.getInt("delayDays");
            String applicationStatus  = resultSet.getString("applicationStatus");
            DelayedApplication delayedApplication = new DelayedApplication(delayDays,delayOfMoney,applicationStatus);
            Order order = new Order(orderNo, address, cost, phoneNumber,consigneeName,
                        createDatetime.toLocalDateTime().format(dtf),orderStatus,payType,
                    productModels,customer,delayedApplication,showForCustomer);
            orders.add(order);
        }
        JdbcHelper.close(resultSet,preparedStatement);
        return orders;
    }
    /**
     * @author 傅立贤
     * @param conn    数据库连接对象
     * @param order 增加对象
     * @return rowsAffected 影响的行数
     * @throws SQLException
     */
    public String add(Order order, Connection conn) throws SQLException {
        //使用预编译创建SQL语句
        String sp_orderform = "CALL sp_orderform(?,?,?,?,?,?,?,?,?)";
        CallableStatement callableStatement = conn.prepareCall(sp_orderform);
        callableStatement.setString(1, "XG");
        callableStatement.setString(2, order.getAddress());
        callableStatement.setDouble(3, order.getCost());
        callableStatement.setString(4, order.getPhoneNumber());
        callableStatement.setString(5, order.getConsigneeName());
        callableStatement.setString(6, order.getOrderStatus());
        callableStatement.setInt(7, order.getPayType().getId());
        callableStatement.setInt(8, order.getCustomer().getId());
        callableStatement.executeUpdate();
        String no = callableStatement.getString(9);
        JdbcHelper.close(callableStatement);
        return no;
    }
    /**
     * @author 傅立贤
     * @param conn    数据库连接对象
     * @return rowsAffected 影响的行数
     * @throws SQLException
     */
    public boolean updateOrderStatus(Order order,Connection conn) throws SQLException {
        //sql语句
        String delay_sql = "update orderForm set orderStatus=? where orderNo = ?";
        PreparedStatement pstmt = conn.prepareStatement(delay_sql);
        pstmt.setString(1,order.getOrderStatus());
        pstmt.setString(2,order.getOrderNo());
        int rowsAffected = pstmt.executeUpdate();
        JdbcHelper.close(pstmt);
        return rowsAffected>0;
    }
    /**
     * @author 傅立贤
     * @param conn    数据库连接对象
     * @param order 需要更改的订单
     * @return rowsAffected 影响的行数
     * @throws SQLException
     */
    public boolean update(Order order,Connection conn) throws SQLException {
        //sql语句
        String delay_sql = "update orderForm set phoneNumber=?,address=?," +
                "consigneeName=?,showForCustomer=?,orderStatus=? where orderNo = ?";
        PreparedStatement pstmt = conn.prepareStatement(delay_sql);
        pstmt.setString(1,order.getPhoneNumber());
        pstmt.setString(2,order.getAddress());
        pstmt.setString(3,order.getConsigneeName());
        pstmt.setBoolean(4,order.getShow());
        pstmt.setString(6,order.getOrderNo());
        pstmt.setString(5,order.getOrderStatus());
        System.out.println(pstmt);
        int rowsAffected = pstmt.executeUpdate();
        JdbcHelper.close(pstmt);
        return rowsAffected>0;
    }
    /**
     * @author 傅立贤
     * @param orderNo 查找的orderNo 订单号
     * @return order 查找到的订单
     * @throws SQLException
     */
    public Order find(String orderNo,Connection conn) throws SQLException {
        //sql语句
        String find_sql = "select * from orderform,delayedApplication " +
                "where orderform.orderNo = delayedApplication.orderNo and orderform.orderNo=?";
        PreparedStatement pstmt = conn.prepareStatement(find_sql);
        pstmt.setString(1,orderNo);
        ResultSet resultSet = pstmt.executeQuery();
        Order order = null;
        if (resultSet.next()) {
            String consigneeName = resultSet.getString("consigneeName");
            String address = resultSet.getString("address");
            String phoneNumber = resultSet.getString("phoneNumber");
            double cost = resultSet.getDouble("cost");
            Integer payType_id = resultSet.getInt("payType_id");
            PayType payType = PayTypeService.getInstance().find(payType_id);
            String orderStatus = resultSet.getString("orderStatus");
            Timestamp timestamp = resultSet.getTimestamp("createDatetime");
            DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            int customer_id = resultSet.getInt("customer_id");
            Customer customer = CustomerDao.getInstance().findInfo(customer_id, conn);
            Collection<ProductModel> productModels = OrderAssProductModelDao.getInstance().find(orderNo, conn);
            double delayOfMoney  = resultSet.getDouble("delayOfMoney");
            boolean showForCustomer = resultSet.getBoolean("showForCustomer");
            Integer delayDays  = resultSet.getInt("delayDays");
            String applicationStatus  = resultSet.getString("applicationStatus");
            Timestamp applyDate_timestamp = resultSet.getTimestamp("applyDate");
            DelayedApplication delayedApplication = new DelayedApplication(delayDays,
                    applyDate_timestamp.toLocalDateTime().format(dtf),delayOfMoney,applicationStatus);
            order = new Order(orderNo,address,cost,consigneeName,
                    phoneNumber,timestamp.toLocalDateTime().format(dtf),
                    orderStatus,payType,productModels,customer,delayedApplication,showForCustomer);
        }
        JdbcHelper.close(pstmt);
        return order;
    }
}